"""
Case Type   : GSC功能模块
Case Name   : gsc打开，非线程池模式下，并行/串行连接相同db，gsc缓存占用比对验证
Create At   : 2022/4/27
Owner       : opentestcase024
Description :
    1、修改enable_global_syscache为on;enable_thread_pool为off;
    2、重启数据库，使参数生效;
    3、创建数据库;
    4、新建库进行连接;
    5、查看syscache占用情况;
    6、多次串行对新建库进行连接;
    7、查看syscache占用情况;
    8、多次并行对新建库进行连接;
    9、查看syscache占用情况;
Expect      :
    1、修改enable_global_syscache为on;enable_thread_pool为off; 成功
    2、重启数据库，使参数生效; 重启成功
    3、创建数据库; 创建成功
    4、新建库进行连接; 连接成功
    5、查看syscache占用情况; 查询成功
    6、多次串行对新建库进行连接; 连接成功
    7、查看syscache占用情况; gsc缓存占用不变
    8、多次并行对新建库进行连接; 连接成功
    9、查看syscache占用情况; gsc缓存占用不变
History     :
    Modified by peilinqian at 2025/1/21:teardown增加集群主备恢复动作，以防对其他用例产生影响
    modified：2023-4-21 by opentestcase024;精简用例，减少其他库影响，只查询新建库的缓存信息；
"""
import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'{os.path.basename(__file__)}:start')
        self.sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.db_name = 'db_gsc0017'
        self.circle_num = 10

    def test_main(self):
        step_txt = 'step0:查看enable_global_syscache初始配置值;'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        step_txt = 'step0:查看enable_thread_pool初始值;'
        self.log.info(step_txt)
        self.init_para2 = self.com.show_param('enable_thread_pool')

        step_txt = 'step1:修改enable_global_syscache为on;' \
                   'enable_thread_pool为off; expect:成功'
        self.log.info(step_txt)
        msg = self.sh.execute_gsguc('set',
                                    self.constant.GSGUC_SUCCESS_MSG,
                                    f"enable_global_syscache= on")
        self.assertTrue(msg, '执行失败:' + step_txt)
        msg = self.sh.execute_gsguc('set',
                                    self.constant.GSGUC_SUCCESS_MSG,
                                    f"enable_thread_pool= off")
        self.assertTrue(msg, '执行失败:' + step_txt)

        step_txt = 'step2:重启数据库，使参数生效; expect:重启成功'
        self.log.info(step_txt)
        restart_result = self.sh.restart_db_cluster()
        self.assertTrue(restart_result)
        self.log.info('查询参数')
        self.new_para1 = self.com.show_param('enable_global_syscache')
        self.assertEqual(self.new_para1, 'on', '执行失败:' + step_txt)
        self.new_para2 = self.com.show_param('enable_thread_pool')
        self.assertEqual(self.new_para2, 'off', '执行失败:' + step_txt)

        step_txt = f'step3:创建数据库; expect:创建成功'
        self.log.info(step_txt)
        create_db = f'create database {self.db_name};'
        create_result = self.sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                      create_result, '执行失败:' + step_txt)

        step_txt = 'step4:新建库进行连接; expect:连接成功'
        db_oid = f"(select cast((select oid from pg_database where " \
            f"datname = '{self.db_name}') as text))"
        con_sql = f"select current_database();" \
            f"select pg_sleep(3);" \
            f"select * from gs_gsc_memory_detail where db_id ={db_oid};"
        self.log.info(step_txt)
        con_result = self.sh.execut_db_sql(con_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(con_result)
        self.assertIn(self.db_name, con_result, '执行失败:' + step_txt)

        step_txt = 'step5:查看syscache占用情况; expect:查询成功'
        self.log.info(step_txt)
        check_sql = f"select totalsize from gs_gsc_memory_detail " \
            f"where db_id ={db_oid}; select * from gs_gsc_dbstat_info() " \
            f"where database_id={db_oid}; "
        check_result = self.sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        total_size1 = int(check_result.splitlines()[2].split()[0].strip())
        self.assertGreater(total_size1, 0, '执行失败:' + step_txt)

        step_txt = 'step6:多次串行对新建库进行连接; expect:连接成功'
        self.log.info(step_txt)
        for i in range(self.circle_num):
            con_result = self.sh.execut_db_sql(con_sql,
                                               dbname=f'{self.db_name}')
            self.log.info(con_result)
            self.assertIn(self.db_name, con_result, '执行失败:' + step_txt)

        step_txt = 'step7:查看syscache占用情况; expect:gsc缓存占用不变'
        self.log.info(step_txt)
        check_result = self.sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        total_size2 = int(check_result.splitlines()[2].split()[0].strip())
        self.assertEqual(total_size2, total_size1, '执行失败:' + step_txt)

        step_txt = 'step8:多次并行对新建库进行连接; expect:连接成功'
        self.log.info(step_txt)
        connect_thread = []
        for i in range(self.circle_num):
            connect_thread.append(ComThread(self.sh.execut_db_sql,
                                            args=(con_sql, '',
                                                  f'{self.db_name}')))
            connect_thread[i].setDaemon(True)
            connect_thread[i].start()
        for i in range(self.circle_num):
            connect_thread[i].join(20)
            result = connect_thread[i].get_result()
            self.log.info(result)

        step_txt = 'step9:查看syscache占用情况; expect:gsc缓存占用不变'
        self.log.info(step_txt)
        check_result = self.sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        total_size3 = int(check_result.splitlines()[2].split()[0].strip())
        self.assertEqual(total_size2, total_size3, '执行失败:' + step_txt)

    def tearDown(self):
        step_txt = 'this is teardown'
        self.log.info(step_txt)
        step1_txt = '删除数据库 expect:删除成功'
        self.log.info(step1_txt)
        drop_db = f'drop database {self.db_name}'
        drop_db_result = self.sh.execut_db_sql(drop_db)

        step2_txt = '恢复参数为初始值并查询; expect:设置成功'
        self.log.info(step2_txt)
        msg1 = self.sh.execute_gsguc('set',
                                     self.constant.GSGUC_SUCCESS_MSG,
                                     f"enable_global_syscache="
                                     f"{self.init_para1}")
        msg2 = self.sh.execute_gsguc('reload',
                                     self.constant.GSGUC_SUCCESS_MSG,
                                     f"enable_thread_pool="
                                     f"{self.init_para2}")
        step3_txt = '重启数据库，使参数生效; expect:重启成功'
        self.log.info(step3_txt)
        restart_result = self.sh.restart_db_cluster()
        step4_txt = '----查询数据库状态; expect:状态正常----'
        self.log.info(step4_txt)
        status = self.sh.exec_cm_ctl(mode='query', param='-Cv')
        self.log.info(status)
        p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
        if 'Primary' in p_status and "cluster_state   : Normal" in status:
            self.log.info('---集群状态正常，无需恢复为初始状态---')
        else:
            recovery = self.sh.exec_cm_ctl(mode='switchover',
                                           param='-a')
            self.log.info(recovery)
            time.sleep(10)
        status_result = self.sh.get_db_cluster_status('status')

        self.log.info(f'{os.path.basename(__file__)}:end')
        flag = self.constant.DROP_DATABASE_SUCCESS
        self.assertIn(flag, drop_db_result, '执行失败:' + step_txt + step1_txt)
        self.assertTrue(msg1, '执行失败:' + step_txt + step2_txt)
        self.assertTrue(msg2, '执行失败:' + step_txt + step2_txt)
        self.assertTrue(restart_result, '执行失败:' + step_txt + step3_txt)
        self.assertTrue(status_result, '执行失败:' + step_txt + step4_txt)
